﻿using HRlink.Hilton.JobCode.Collector.Common;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using ZenHR.AttData.DAL;
using ZenHR.AttData.Collection.DataEntity;
using ZenHR.AttData.DAL.DB3Helper;
using ZenHR.AttData.Collection.LinkDBHelper;
using ZenHR.AttData.Collection.TaskQueue;
using static System.Net.Mime.MediaTypeNames;
using System.Configuration;
using ZenHR.AttData.Collection.Util;
using static ZenHR.AttData.Collection.LinkDBHelper.AnalyzeStrHelper;
using System.IO;
using System.Data;
using System.Reflection;
using Renci.SshNet;

namespace ZenHR.AttData.Collection.AttDataRun
{
    public  class DataRunScript
    {
        public delegate void UpdateUI(int step, int all_steps, string log_msg);//声明一个更新主线程的委托
        public UpdateUI UpdateUIDelegate;

        public delegate void AccomplishTask();//声明一个在完成任务时通知主线程的委托
        public  AccomplishTask TaskCallBack;

        public void Run(object _task)
        {
            var _TaskRunTimeInfo = (_task as TaskRunTimeInfo);
            try
            {
                Console.WriteLine("Task[DataRunScript]：" + _TaskRunTimeInfo.Task_code + "start runing...");
                Thread.Sleep(2000);

                BaseDAL _BaseDAL = new BaseDAL();
                var _batchID = DateTime.Now.ToString("yyyyMMddHHmm");

                var _msg = "";

                var _Sql_type = "";
                try
                {
                    _Sql_type = _TaskRunTimeInfo.Sql_type.Split('^')[0];
                }
                catch (Exception)
                {
                    _Sql_type = _TaskRunTimeInfo.Sql_type;
                }

                var _Suffix = "";

                try
                {
                    _Suffix = _TaskRunTimeInfo.Sql_type.Split('^')[2];
                }
                catch (Exception)
                {
                    _Suffix = "";
                }

                switch (_Sql_type)
                {
                    case  "MsSql":
                        //执行的sql语句中如有{0}用current_index替换
                        _TaskRunTimeInfo.Sql_str= string.Format(_TaskRunTimeInfo.Sql_str,_TaskRunTimeInfo.Current_index);

                        //获取待插入记录
                        var _runTable = MsSqlHelper.ExecuteDataTable(_TaskRunTimeInfo.Sql_str, _TaskRunTimeInfo.ConnectionString);
                        try
                        {
                            _msg = _BaseDAL.BatchInsertTaskAttData(_TaskRunTimeInfo.Task_DB, _batchID, _TaskRunTimeInfo.Id, _TaskRunTimeInfo.Task_code, _runTable);
                        }
                        catch (Exception ex)
                        {
                            LogHelper.WriteLog("Task BatchInsertTaskAttData ERROR：" + ex.Message);
                        }
                      
                        break;
                    case "MySql":
                        //执行的sql语句中如有{0}再用current_index替换
                        _TaskRunTimeInfo.Sql_str = string.Format(_TaskRunTimeInfo.Sql_str, _TaskRunTimeInfo.Current_index);

                        //获取待插入记录
                        var _runTable2 = MySqlHelper.GetDataTable(_TaskRunTimeInfo.ConnectionString,System.Data.CommandType.Text, _TaskRunTimeInfo.Sql_str);
                        _msg = _BaseDAL.BatchInsertTaskAttData(_TaskRunTimeInfo.Task_DB, _batchID, _TaskRunTimeInfo.Id,_TaskRunTimeInfo.Task_code, _runTable2);

                        break;
                    case "txt":
                        //获取待插入记录
                        var _runTable3 = AnalyzeStrHelper.ExecuteDataTable(_TaskRunTimeInfo.Sql_str, _TaskRunTimeInfo.ConnectionString,"", int.Parse(_TaskRunTimeInfo.Current_index), _Suffix);
                        _msg = _BaseDAL.BatchInsertTaskAttData(_TaskRunTimeInfo.Task_DB, _batchID, _TaskRunTimeInfo.Id, _TaskRunTimeInfo.Task_code, _runTable3);

                        break;
                    case "share_txt":
                        //解析json分析得出下列参数
                        var _sharedinfo = JsonHelper.ToObject<ShareLinkInfo>(_TaskRunTimeInfo.ConnectionString);
                        var _ip = _sharedinfo.Ip;
                        var _user = _sharedinfo.User;
                        var _pwd = _sharedinfo.Pwd;
                        var _ignore = _sharedinfo.Ignore;  //忽略文件（指定一个文件不采集）

                        string selectPath = _sharedinfo.Path.Replace(@"\\",@"\");
                        selectPath = @"\" + selectPath;

                        bool status = false;

                        status = connectState(@selectPath, _user, _pwd);

                        if (status)
                        {
                            //获取待插入记录
                            var _runTable4 = AnalyzeStrHelper.ExecuteDataTable(_TaskRunTimeInfo.Sql_str, @selectPath,_ignore,int.Parse(_TaskRunTimeInfo.Current_index));
                            _msg = _BaseDAL.BatchInsertTaskAttData(_TaskRunTimeInfo.Task_DB,_batchID, _TaskRunTimeInfo.Id, _TaskRunTimeInfo.Task_code, _runTable4);

                        }

                        //using (SharedTool tool = new SharedTool(_user, _pwd, _ip))
                        //{
                        //    //获取待插入记录
                        //    var _runTable4 = AnalyzeStrHelper.ExecuteDataTable(_TaskRunTimeInfo.Sql_str, @selectPath);
                        //    _msg = _BaseDAL.BatchInsertTaskAttData(_batchID, _TaskRunTimeInfo.Id, _TaskRunTimeInfo.Task_code, _runTable4);
                        //}
                      
                        break;

                    case "ZK":
                        var _ZKdinfo = JsonHelper.ToObject<ZKLinkInfo>(_TaskRunTimeInfo.ConnectionString);
                        var _zk_ip = _ZKdinfo.Ip;
                        var _zk_port = _ZKdinfo.Port;
                        var _zk_no = _ZKdinfo.Machine_no;
                        var _zk_type = _ZKdinfo.Machine_type;

                        var _Mlist = new List<MachineInfo>();
                        _Mlist.Add(new MachineInfo { Ip = _zk_ip, Port = Convert.ToInt32(_zk_port), Machine_no = Convert.ToInt32(_zk_no), Machine_type = _zk_type });

                        var _Log = "";

                        var _ZKHelper = new ZKHelper();
                        var _aa= _ZKHelper.GetAttendanceData(_Mlist,  ref _Log);

                        var _runTable5 = ListToDataTable(_aa);
                        _msg = _BaseDAL.BatchInsertTaskAttData(_TaskRunTimeInfo.Task_DB,_batchID, _TaskRunTimeInfo.Id, _TaskRunTimeInfo.Task_code, _runTable5);

                        break;

                    case "sftp":
                        //获取解析获取SFTP对应的Json配置    Json样例：{"Host":"sftp.example.com","Port":"22","Username":"username","Password":"password","Directory":"/remote/path/to/files/","Loaddays":3}
                        var _SFTPinfo = JsonHelper.ToObject<SftpLinkInfo>(_TaskRunTimeInfo.ConnectionString);

                        DateTime now = DateTime.Now;

                        var _sftp_host = _SFTPinfo.Host;// "sftp.example.com";
                        var _sftp_port = _SFTPinfo.Port;//22;
                        var _sftp_username = _SFTPinfo.Username;//"username";
                        var _sftp_password = _SFTPinfo.Password;//"password";
                        var _sftp_directory = _SFTPinfo.Directory;//"/remote/path/to/files/";
                        var _sftp_loaddays = _SFTPinfo.Loaddays ;// 3;

                        using (var client = new SftpClient(_sftp_host, Convert.ToInt32(_sftp_port), _sftp_username, _sftp_password))
                        {
                            client.Connect();
                            var fileList = client.ListDirectory(_sftp_directory);
                            foreach (var file in fileList)
                            {
                                if (!file.IsDirectory && file.Name.EndsWith(".csv"))
                                {
                                    //获取N天内生成的文件
                                    if (file.LastWriteTime > new DateTime(now.Year, now.Month, now.Day).AddDays(_sftp_loaddays * -1))
                                    {
                                        var fileContent = client.ReadAllText(_sftp_directory + file.Name);
                                        Console.WriteLine(fileContent);
                                        LogHelper.WriteLog("Task Run SftpClientLoad：" + fileContent);

                                        //读取CSV文件并转换成DataTable
                                        string filePath = _sftp_directory + "/" + file.Name;
                                        DataTable _dataTable6 = ReadCsvFile(client, filePath);
                                        LogHelper.WriteLog("Task Run SftpClientLoad：" + _dataTable6.Rows.Count + " rows loaded from " + filePath);

                                        _msg = _BaseDAL.BatchInsertTaskAttData(_TaskRunTimeInfo.Task_DB, _batchID, _TaskRunTimeInfo.Id, _TaskRunTimeInfo.Task_code, _dataTable6);

                                    }
                                    else {
                                        LogHelper.WriteLog("Task Run SftpClientLoad：" + "No recently generated files have been read");
                                    }

                                }
                            }
                            client.Disconnect();
                        }

                        break;
                    default:
                        break;
                }

                if (_msg != "")
                {
                    LogHelper.WriteLog("Task Run BatchInsertTaskAttData：" + _msg);
                }

                Console.WriteLine("Task [DataRunScript]：" + _TaskRunTimeInfo.Task_code + "end...");

                RunPackageProcess(_TaskRunTimeInfo.Task_DB, _TaskRunTimeInfo.Current_index,  _TaskRunTimeInfo.Id,  _TaskRunTimeInfo.Task_code,  _batchID,  _TaskRunTimeInfo.Sql_type,  _TaskRunTimeInfo.Mail_address, _TaskRunTimeInfo.Task_DB, _BaseDAL);

                //任务完成时通知主线程作出相应的处理
                TaskCallBack();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Task DataRunScript.Run ERROR_" + _TaskRunTimeInfo.Task_code + ":" + ex.Message);
                //MailHelper.Send(_TaskRunTimeInfo.Error_Mail, "DataRunScript Error,_task:" + _task.ToString(), ex.Message);
            }
        }

        // 定义读取CSV文件的方法
        static DataTable ReadCsvFile(SftpClient sftp, string filePath)
        {
            DataTable dataTable = new DataTable();
            Stream fileStream = sftp.OpenRead(filePath);

            using (StreamReader reader = new StreamReader(fileStream))
            {
                // 读取表头
                string header = reader.ReadLine();
                string[] headerColumns = header.Split(',');
                foreach (string column in headerColumns)
                {
                    dataTable.Columns.Add(column);
                }

                // 读取数据
                while (!reader.EndOfStream)
                {
                    string dataRow = reader.ReadLine();
                    string[] dataColumns = dataRow.Split(',');
                    DataRow row = dataTable.NewRow();
                    for (int i = 0; i < headerColumns.Length; i++)
                    {
                        row[i] = dataColumns[i];
                    }
                    dataTable.Rows.Add(row);
                }
            }

            return dataTable;
        }

        public static DataTable ListToDataTable<T>(List<T> entitys)
        {

            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                return new DataTable();
            }

            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable("dt");
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }

            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);

                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }


        public static bool connectState(string path)
        {
            return connectState(path, "", "");
        }
        /// <summary>
        /// 连接远程共享文件夹
        /// </summary>
        /// <param name="path">远程共享文件夹的路径</param>
        /// <param name="userName">用户名</param>
        /// <param name="passWord">密码</param>
        /// <returns></returns>
        public static bool connectState(string path, string userName, string passWord)
        {
            bool Flag = false;
            Process proc = new Process();
            try
            {
                proc.StartInfo.FileName = "cmd.exe";
                proc.StartInfo.UseShellExecute = false;
                proc.StartInfo.RedirectStandardInput = true;
                proc.StartInfo.RedirectStandardOutput = true;
                proc.StartInfo.RedirectStandardError = true;
                proc.StartInfo.CreateNoWindow = true;
                proc.Start();
                string dosLine = "net use " + path + " " + passWord + " /user:" + userName;
                proc.StandardInput.WriteLine(dosLine);
                proc.StandardInput.WriteLine("exit");
                while (!proc.HasExited)
                {
                    proc.WaitForExit(1000);
                }
                string errormsg = proc.StandardError.ReadToEnd();
                proc.StandardError.Close();
                if (string.IsNullOrEmpty(errormsg))
                {
                    Flag = true;
                }
                else
                {
                    throw new Exception(errormsg);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                proc.Close();
                proc.Dispose();
            }
            return Flag;
        }

        /// <summary>
        /// 向远程文件夹保存本地内容，或者从远程文件夹下载文件到本地
        /// </summary>
        /// <param name="src">要保存的文件的路径，如果保存文件到共享文件夹，这个路径就是本地文件路径如：@"D:\1.avi"</param>
        /// <param name="dst">保存文件的路径，不含名称及扩展名</param>
        /// <param name="fileName">保存文件的名称以及扩展名</param>
        public static void Transport(string src, string dst, string fileName)
        {
            FileStream inFileStream = new FileStream(src, FileMode.Open);
            if (!Directory.Exists(dst))
            {
                Directory.CreateDirectory(dst);
            }
            dst = dst + fileName;
            FileStream outFileStream = new FileStream(dst, FileMode.OpenOrCreate);

            byte[] buf = new byte[inFileStream.Length];

            int byteCount;

            while ((byteCount = inFileStream.Read(buf, 0, buf.Length)) > 0)
            {
                outFileStream.Write(buf, 0, byteCount);
            }

            inFileStream.Flush();
            inFileStream.Close();
            outFileStream.Flush();
            outFileStream.Close();
        }

        private void RunPackageProcess(string _TaskRunTimeInfo_Current_Task_db, string _TaskRunTimeInfo_Current_index, string _TaskRunTimeInfo_Id, string _TaskRunTimeInfo_Task_code, string _batchID ,string _TaskRunTimeInfo_Sql_type ,string _TaskRunTimeInfo_Mail_address, string _TaskRunTimeInfo_TaskDB, BaseDAL _BaseDAL)
        {
            try
            {
                //调用打包进程处理打包对比及上传ZenHR
                Process _isupdate = new Process();

                //_isupdate.StartInfo.FileName = AppDomain.CurrentDomain.BaseDirectory + "\\ZenHR.AttData.Package.exe";
                Process.Start(AppDomain.CurrentDomain.BaseDirectory + "\\ZenHR.AttData.Package.exe", _TaskRunTimeInfo_Id + " " + _TaskRunTimeInfo_Task_code + " " + _batchID + " " + _TaskRunTimeInfo_Sql_type + " " + _TaskRunTimeInfo_Mail_address + " " + _TaskRunTimeInfo_TaskDB);

                //获取对应采集记录表中最大的记录号更新至task_run_time_info配置表的current_index中,当Current_index<0则表示不启用更新这个自增字段，脚本配合不用到这个参数
                if (int.Parse(_TaskRunTimeInfo_Current_index) >= 0)
                {
                    _BaseDAL.UpdateCurrentIndex(_TaskRunTimeInfo_Current_Task_db,_TaskRunTimeInfo_Task_code);
                }
            }
            catch (Exception ex )
            {
                LogHelper.WriteLog("Task DataRunScript.Run RunPackageProcess ERROR：" + ex.Message);

                Thread.Sleep(2000);
            }

        }
    }
}
